This analysis explores the relationships of agricultural commodity loss, at a county level, from 2001-2015, across the three state region of the Pacific Northwest (Washington, Idaho, and Oregon), and then focuses on the Inland Pacific Northwest (INPW - 26 county region that encapsulates portions of Washington, Idaho, and Oregon).
Here we explore the entire range of commodities and damage causes, identifying the top revenue loss commodities and their most pertinent damage causes - as indicated from the USDA’s agricultural commodity loss insurance archive. We also explore claim freqency, and finally zero in on the top 5 commodities for the region, and examine specific loss and claim frequency by year and damage cause.
We perform several steps to examine the full set of data, and to narrow down factors to a usable form for further modeling analysis.
Step 1. Data Preparation: Loading and Transforming data. Here we load commodity loss data, and aggregate by differing factors, so we can examine losses on an annual basis, by county, damage cause, and commodity.
Step 2. Pacific Northwest (PNW) Overview. Here we examine overall commodity loss for the entire three state region of Washington, Idaho, and Oregon, which comes primarily from three primary agricultural regions - the IPNW (mostly Washington, with portions of northern Idaho and northeastern Oregon), the Southern Idaho Valley (Idaho), and the Willamette Valley (Oregon).
Step 3. PNW region overview of insurance loss by year, damage cause, and commodity, and county: 2001-2015
Step 4. PNW region overview of insurance Loss claim counts by year, damage cause, commodity, and county: 2001-2015
Step 5. Inland Pacific Northwest (IPNW) Overview. Given our review of insurance loss for the entire PNW - with a large proportion of loss occuring in the IPNW, were here focus on this region and more specifically examine loss and claim counts.
Step 6. IPNW region overview of Insurance Loss FREQUENCY by commodity and damage cause: 2001-2015
Step 7. IPNW region overview of Insurance Loss claim counts: 2001-2015
Step 8. APPLES: IPNW region overview of Insurance Loss 2001-2015
Step 9. BARLEY: IPNW region overview of Insurance Loss 2001-2015
Step 10. WHEAT: IPNW region overview of Insurance Loss 2001-2015
Step 11. CHERRIES: IPNW region overview of Insurance Loss 2001-2015
Step 12. DRY PEAS: IPNW region overview of Insurance Loss 2001-2015
The first step is to load Pacific Northwest agricultural insurance commodity loss data, aggregate by county, damage cause and commodity, remove zeros, transform the loss values ($) into cube root and logrithmic outputs, and scale and center the data.
Original data file for all insurance claims for the Pacific Northwest, can be found here:
https://github.com/erichseamon/paper-PHD1/blob/master/data/RMA_PNW_2001_2015.csv
Aggregated data file, which summarizes data by year, county, commodity, and damage cause, for just the Pacific Northwest, can be found here:
https://github.com/erichseamon/paper-PHD1/blob/master/data/RMA_damage_PNW_2001_2015.csv
All code that generates the following graphs and tables, can be found here:
https://github.com/erichseamon/paper-PHD1/
The following table is an example of the original data that was acquired from the USDA Risk Management Agency (RMA). Each record represents an individual insurance claim.
| year | state | county | commodity | damagecause | monthcode | month | acres | loss | |
|---|---|---|---|---|---|---|---|---|---|
| 11338 | 2001 | ID | Ada | All Other Crops | Drought | 9 | SEP | 17.000 | 153.00 |
| 11339 | 2001 | ID | Ada | All Other Crops | Heat | 8 | AUG | 105.200 | 5249.00 |
| 11340 | 2001 | ID | Ada | All Other Crops | Freeze | 4 | APR | 125.000 | 4500.00 |
| 11341 | 2001 | ID | Ada | All Other Crops | Wind/Excess Wind | 5 | MAY | 50.000 | 1800.00 |
| 11342 | 2001 | ID | Ada | All Other Crops | Wind/Excess Wind | 4 | APR | 92.500 | 3330.00 |
| 11343 | 2001 | ID | Bannock | WHEAT | Drought | 8 | AUG | 133.000 | 1212.00 |
| 11344 | 2001 | ID | Bannock | WHEAT | Drought | 9 | SEP | 777.520 | 24807.00 |
| 11345 | 2001 | ID | Bannock | WHEAT | Drought | 7 | JUL | 3529.754 | 54726.46 |
| 11346 | 2001 | ID | Bannock | WHEAT | Heat | 7 | JUL | 19.796 | 2371.60 |
| 11347 | 2001 | ID | Bannock | WHEAT | Heat | 8 | AUG | 25.000 | 904.00 |
The following table is an example of an aggregated dataset derived from the original files. Here we have summarized claims by year, county, commodity, and damage cause. Each unique combination is summarized, which includes the total summarized loss, the number of claims, the total summarized acreage, loss per acre, loss per claim, and acres per claim. This dataset was the basis for our data examination.
| year | state | county | commodity | damagecause | loss | count | acres | lossperacre | lossperclaim | acresperclaim | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 11 | 2014 | ID | Ada | All Other Crops | Area Plan Crops Only | 1398 | 1 | 0 | 0.0000000 | 1398.0 | 0 |
| 12 | 2015 | ID | Ada | All Other Crops | Area Plan Crops Only | 11810 | 1 | 0 | 0.0000000 | 11810.0 | 0 |
| 211 | 2008 | OR | Baker | All Other Crops | Area Plan Crops Only | 15292 | 2 | 5482 | 2.7894929 | 7646.0 | 2741 |
| 212 | 2010 | OR | Baker | All Other Crops | Area Plan Crops Only | 1819 | 2 | 2282 | 0.7971078 | 909.5 | 1141 |
| 215 | 2009 | ID | Bannock | All Other Crops | Area Plan Crops Only | 2284 | 1 | 600 | 3.8066667 | 2284.0 | 600 |
| 245 | 2008 | ID | Bear Lake | All Other Crops | Area Plan Crops Only | 8102 | 1 | 2468 | 3.2828201 | 8102.0 | 2468 |
| 246 | 2012 | ID | Bear Lake | All Other Crops | Area Plan Crops Only | 7459 | 1 | 2468 | 3.0222853 | 7459.0 | 2468 |
| 291 | 2010 | ID | Bingham | All Other Crops | Area Plan Crops Only | 4197 | 1 | 192 | 21.8593750 | 4197.0 | 192 |
| 292 | 2011 | ID | Bingham | All Other Crops | Area Plan Crops Only | 7769 | 1 | 240 | 32.3708333 | 7769.0 | 240 |
| 293 | 2012 | ID | Bingham | All Other Crops | Area Plan Crops Only | 6786 | 1 | 168 | 40.3928571 | 6786.0 | 168 |
The following map shows the three key agricultural regions that make up over 95% of all agricultural insurance loss for the three state region of Washington, Oregon, and Idaho.
| PNW total insurance loss by year, 2001-2015 | |
| 2001-2015 | |
|---|---|
| damage cause | loss |
| Drought | $719,610,396 |
| Decline in Price | $336,993,981 |
| Heat | $267,376,106 |
| Frost | $170,482,583 |
| Freeze | $149,373,220 |
| Excess Moisture/Precip/Rain | $148,556,853 |
| Hail | $127,772,100 |
| Cold Wet Weather | $86,741,055 |
| Cold Winter | $67,118,523 |
| Failure Irrig Supply | $57,087,916 |
| PNW total insurance loss by year, 2001-2015 | |
| 2001-2015 | |
|---|---|
| commodity | loss |
| WHEAT | $1,444,149,995 |
| CHERRIES | $156,670,513 |
| APPLES | $156,063,380 |
| POTATOES | $117,459,400 |
| BARLEY | $102,125,046 |
| DRY PEAS | $58,038,162 |
| PNW total insurance loss by year, 2001-2015 | |
| 2001-2015 | |
|---|---|
| year | loss |
| 2001 | $66,499,267 |
| 2002 | $96,367,644 |
| 2003 | $103,553,264 |
| 2004 | $52,006,242 |
| 2005 | $72,871,250 |
| 2006 | $63,467,130 |
| 2007 | $83,252,301 |
| 2008 | $150,480,619 |
| 2009 | $545,650,029 |
| 2010 | $83,829,994 |
| 2011 | $115,965,036 |
| 2012 | $89,684,692 |
| 2013 | $171,121,810 |
| 2014 | $222,526,098 |
| 2015 | $296,416,750 |
## commodity region loss
## 42 DRY PEAS PNW minus IPNW 1228785
## 25 POTATOES IPNW 10232481
## 47 GRAPES PNW minus IPNW 14439829
## 16 GRAPES IPNW 17370434
## 4 BARLEY IPNW 34102839
## 11 DRY PEAS IPNW 56809377
## 34 APPLES PNW minus IPNW 66872348
## 35 BARLEY PNW minus IPNW 68022207
## 8 CHERRIES IPNW 76118306
## 38 CHERRIES PNW minus IPNW 80552207
## 3 APPLES IPNW 89191032
## 57 POTATOES PNW minus IPNW 107226919
## 63 WHEAT PNW minus IPNW 230004351
## 31 WHEAT IPNW 1214145644
| IPNW region total insurance loss by year, 2001-2015 | |
| 2001-2015 | |
|---|---|
| year | loss |
| 2001 | $51,669,997 |
| 2002 | $75,193,597 |
| 2003 | $71,122,910 |
| 2004 | $27,690,033 |
| 2005 | $42,829,865 |
| 2006 | $50,433,271 |
| 2007 | $47,600,330 |
| 2008 | $118,284,490 |
| 2009 | $441,709,248 |
| 2010 | $52,081,926 |
| 2011 | $68,218,646 |
| 2012 | $47,232,701 |
| 2013 | $118,373,608 |
| 2014 | $177,267,234 |
| 2015 | $240,900,597 |
| IPNW region total insurance loss by commodity, 2001-2015 | |
| 2001-2015 | |
|---|---|
| commodity | loss |
| WHEAT | $1,214,345,016 |
| APPLES | $89,191,032 |
| CHERRIES | $74,853,794 |
| DRY PEAS | $56,809,377 |
| BARLEY | $34,102,839 |
| GRAPES | $17,370,434 |
| GREEN PEAS | $13,520,586 |
| POTATOES | $10,232,481 |
| All Other Crops | $9,767,559 |
| CANOLA | $8,913,093 |
| ADJUSTED GROSS REVENUE-LITE | $8,128,725 |
| IPNW region total insurance loss by damage cause, 2001-2015 | |
| 2001-2015 | |
|---|---|
| damage cause | loss |
| Drought | $677,383,459 |
| Decline in Price | $290,497,980 |
| Heat | $197,657,025 |
| Frost | $92,483,544 |
| Hail | $81,461,121 |
| Freeze | $77,583,017 |
| Excessive Moisture | $76,603,640 |
| Cold Wet Weather | $54,410,357 |
| Cold Winter | $33,893,991 |
| Wind/Excess Wind | $12,442,392 |
| IPNW region APPLES total insurance loss by damage cause, 2001-2015 | |
| 2001-2015 | |
|---|---|
| damagecause | loss |
| Hail | $40,971,994 |
| Frost | $17,016,586 |
| Freeze | $14,538,852 |
| Cold Wet Weather | $9,246,848 |
| Wind/Excess Wind | $2,934,044 |
| Heat | $2,422,480 |
| Other | $2,060,230 |
| IPNW region BARLEY total insurance loss by damage cause, 2001-2015 | |
| 2001-2015 | |
|---|---|
| damagecause | loss |
| Drought | $19,635,980 |
| Excess Moisture/Precip/Rain | $4,726,399 |
| Heat | $4,553,252 |
| Hail | $1,466,219 |
| Frost | $1,247,180 |
| Other | $1,085,647 |
| Decline in Price | $858,459 |
| Cold Wet Weather | $529,703 |
| IPNW region WHEAT total insurance loss by damage cause, 2001-2015 | |
| 2001-2015 | |
|---|---|
| damagecause | loss |
| Drought | $626,038,990 |
| Decline in Price | $243,681,710 |
| Heat | $150,761,018 |
| Frost | $45,605,476 |
| Excess Moisture/Precip/Rain | $33,421,663 |
| Cold Winter | $28,004,014 |
| Freeze | $27,203,439 |
| Cold Wet Weather | $24,511,419 |
| Hail | $20,390,532 |
| Other | $9,724,032 |
| Plant Disease | $2,950,763 |
| Other (Snow-Lightning-Etc.) | $2,051,960 |
| IPNW region CHERRIES total insurance loss by damage cause, 2001-2015 | |
| 2001-2015 | |
|---|---|
| damagecause | loss |
| Excessive Moisture | $20,569,890 |
| Frost | $15,459,348 |
| Freeze | $14,625,022 |
| Decline in Price | $8,447,020 |
| Cold Wet Weather | $6,725,206 |
| Heat | $2,246,093 |
| Hail | $2,034,613 |
| Other (Snow-Lightning-Etc.) | $1,876,035 |
| Other | $1,467,713 |
| Wind/Excess Wind | $1,402,854 |
| IPNW region DRY PEAS total insurance loss by damage cause, 2001-2015 | |
| 2001-2015 | |
|---|---|
| damagecause | loss |
| Drought | $22,635,712 |
| Heat | $10,399,165 |
| Excessive Moisture | $8,618,433 |
| Hail | $5,176,545 |
| Cold Wet Weather | $2,971,140 |
| Decline in Price | $2,936,766 |
| Other | $2,067,635 |
| Wind/Excess Wind | $1,079,927 |
| Frost | $924,053 |